package org.cncitrus.data.db;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import orange.database.DBIns;

import org.cncitrus.data.common.Params;
import org.cncitrus.data.exception.CitrusException;
import org.cncitrus.data.manager.ParamsManager;

public class ChinaAreaConn {
//	private Statement stmt;
	private String TABLE_NAME = "orange_z_mianji";
	public ChinaAreaConn(){
//		try {
//			stmt = OrangeConn.getInstance().getStatement();
//		} catch (SQLException e) {
//			System.err.print("ChinaAreaConn:无法实例化Statement");
//			e.printStackTrace();
//		}
	}
	
	public String load(String[] year,String[] pro) throws Exception{
		StringBuffer sb = new StringBuffer();
		String sql = "select * from "+TABLE_NAME+" WHERE nianfen IN "+ParamsManager.Array2String(year)+" AND shengfen IN "+ParamsManager.Array2String(pro);
		Connection conn = DBIns.getInstance().getConnection();
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			sb.append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
			rs.last();
			sb.append("<resp count=\""+rs.getRow()+"\">");
			rs.beforeFirst();
			while(rs.next()){
				sb.append("<item year=\""+rs.getString(Params.ChinaArea.year).trim()+"\" pro=\""+rs.getString(Params.ChinaArea.pro).trim()+"\" t1=\""+rs.getString(Params.ChinaArea.area).trim()+"\"/>");
			}
			sb.append("</resp>");
		} catch (Exception e) {
			// TODO: handle exception
		}
		DBIns.getInstance().release(conn);
		return sb.toString();
	}
	
	public String insert(String[] year,String[] pro,String[] area) throws Exception{
		
		StringBuffer sb = new StringBuffer();
		sb.append("INSERT INTO "+TABLE_NAME+" ("+Params.ChinaArea.year+","+Params.ChinaArea.pro+","+Params.ChinaArea.area+") VALUES ");		
		Connection conn = DBIns.getInstance().getConnection();
		try {
			String content = ParamsManager.getInsertString(year,pro,area);
			sb.append(content);
			Statement stmt = conn.createStatement();
			stmt.execute(sb.toString());
			sb = new StringBuffer();
			sb.append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
			sb.append("<resp rc=\"0\"/>");
		} catch (Exception e) {
			// TODO: handle exception
		}
		DBIns.getInstance().release(conn);	
		return sb.toString();
	}
	
}
